import { BaseModel } from './BaseModel';

export interface File {
  id: number;
  original_name: string;
  file_name: string;
  file_path: string;
  file_size: number;
  mime_type: string;
  storage_type: 'local' | 'minio' | 'qiniu';
  category: string;
  bucket_name?: string;
  file_hash?: string;
  url?: string;
  status: 'active' | 'deleted';
  created_by?: number;
  created_at?: string;
  updated_at?: string;
}

export interface FileCreate {
  original_name: string;
  file_name: string;
  file_path: string;
  file_size: number;
  mime_type: string;
  storage_type: 'local' | 'minio' | 'qiniu';
  category: string;
  bucket_name?: string;
  file_hash?: string;
  created_by?: number;
}

export interface FileUpdate {
  original_name?: string;
  file_name?: string;
  file_path?: string;
  file_size?: number;
  mime_type?: string;
  storage_type?: 'local' | 'minio' | 'qiniu';
  category?: string;
  bucket_name?: string;
  status?: 'active' | 'deleted';
}

export class FileModel extends BaseModel {
  protected static tableName = 'files'

  /**
   * 根据分类获取文件列表
   */
  static async findByCategory(category: string, limit: number = 50, offset: number = 0) {
    const limitInt = Math.floor(limit);
    const offsetInt = Math.floor(offset);
    
    const query = `
      SELECT * FROM files 
      WHERE category = '${category}' AND status = 'active' 
      ORDER BY created_at DESC 
      LIMIT ${limitInt} OFFSET ${offsetInt}
    `;
    
    const result = await this.query<File[]>(query);
    return result[0] || [];
  }

  /**
   * 根据存储类型获取文件列表
   */
  static async findByStorageType(storageType: 'local' | 'minio' | 'qiniu', limit: number = 50, offset: number = 0) {
    // 确保参数为安全整数
    const limitInt = Math.max(1, Math.min(1000, Math.floor(Number(limit))));
    const offsetInt = Math.max(0, Math.floor(Number(offset)));
    
    const query = `
      SELECT * FROM files 
      WHERE storage_type = ? AND status = 'active' 
      ORDER BY created_at DESC 
      LIMIT ${limitInt} OFFSET ${offsetInt}
    `;
    
    const result = await this.query<File[]>(query, [storageType]);
    return result[0] || [];
  }

  /**
   * 获取所有文件列表
   */
  static async findAll(limit: number = 50, offset: number = 0) {
    const limitInt = Math.floor(limit);
    const offsetInt = Math.floor(offset);
    
    const query = `
      SELECT * FROM files 
      WHERE status = 'active' 
      ORDER BY created_at DESC 
      LIMIT ${limitInt} OFFSET ${offsetInt}
    `;
    
    const result = await this.query<File[]>(query);
    return result[0] || [];
  }

  /**
   * 根据ID查找文件
   */
  static async findById(id: number | string) {
    const query = `SELECT * FROM files WHERE id = ? AND status = 'active'`;
    const result = await this.query<File[]>(query, [id]);
    return result[0]?.[0] || null;
  }

  /**
   * 创建文件记录
   */
  static async create(data: FileCreate) {
    const query = `
      INSERT INTO files (
        original_name, file_name, file_path, file_size, mime_type, 
        storage_type, category, bucket_name, file_hash, created_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    
    const result = await this.execute(query, [
      data.original_name,
      data.file_name,
      data.file_path,
      data.file_size,
      data.mime_type,
      data.storage_type,
      data.category,
      data.bucket_name || null,
      data.file_hash || null,
      data.created_by || null
    ]);
    
    
    return {
        id: result.insertId,
        status: 'active' as const,
        ...data
    };
  }

  /**
   * 软删除文件（标记为删除状态）
   */
  static async softDelete(id: number) {
    const query = `UPDATE files SET status = 'deleted' WHERE id = ?`;
    const result = await this.execute(query, [id]);
    return result.affectedRows > 0;
  }

  /**
   * 获取用户上传的文件列表
   */
  static async findByUser(userId: number, limit: number = 50, offset: number = 0) {
    // 确保参数为安全整数
    const limitInt = Math.max(1, Math.min(1000, Math.floor(Number(limit))));
    const offsetInt = Math.max(0, Math.floor(Number(offset)));
    
    const query = `
      SELECT * FROM files 
      WHERE created_by = ? AND status = 'active' 
      ORDER BY created_at DESC 
      LIMIT ${limitInt} OFFSET ${offsetInt}
    `;
    
    const result = await this.query<File[]>(query, [userId]);
    return result[0] || [];
  }

  /**
   * 根据文件名查找文件
   */
  static async findByFileName(fileName: string) {
    const query = `SELECT * FROM files WHERE file_name = ? AND status = 'active'`;
    const result = await this.query<File[]>(query, [fileName]);
    return result[0]?.[0] || null;
  }

  /**
   * 根据文件哈希值查找重复文件
   */
  static async findByFileHash(fileHash: string, category: string) {
    const query = `SELECT * FROM files WHERE file_hash = ? AND category = ? AND status = 'active'`;
    const result = await this.query<File[]>(query, [fileHash, category]);
    return result[0]?.[0] || null;
  }

  /**
   * 根据文件大小和分类查找可能的重复文件
   */
  static async findPossibleDuplicates(fileSize: number, category: string, limit: number = 10) {
    // 确保参数为安全整数
    const limitInt = Math.max(1, Math.min(1000, Math.floor(Number(limit))));
    
    const query = `
      SELECT * FROM files 
      WHERE file_size = ? AND category = ? AND status = 'active' 
      ORDER BY created_at DESC 
      LIMIT ${limitInt}
    `;
    
    const result = await this.query<File[]>(query, [fileSize, category]);
    return result[0] || [];
  }

  /**
   * 获取文件统计信息
   */
  static async getStats() {
    const query = `
      SELECT 
        COUNT(*) as total_files,
        COUNT(CASE WHEN storage_type = 'local' THEN 1 END) as local_files,
        COUNT(CASE WHEN storage_type = 'minio' THEN 1 END) as minio_files,
        COUNT(CASE WHEN storage_type = 'qiniu' THEN 1 END) as qiniu_files,
        SUM(file_size) as total_size,
        COUNT(DISTINCT category) as category_count
      FROM files 
      WHERE status = 'active'
    `;
    
    const result = await this.query<any[]>(query);
    return result[0]?.[0] || {
      total_files: 0,
      local_files: 0,
      minio_files: 0,
      qiniu_files: 0,
      total_size: 0,
      category_count: 0
    };
  }
}